use std::{str::FromStr, sync::Arc};

use aho_corasick::AhoCorasick;
use arc_swap::ArcSwapOption;
use chrono::NaiveDate;
use once_cell::sync::Lazy;

use crate::base::{get_work_path, DiviType};

use super::{sql_parse::sql_calc_item_parse, sqls_conf::sum_to_empl};

pub struct CalcSqlItem {
    pub(super) comment: String,
    pub(super) divi_type: DiviType,
    pub(super) ref_columns: Vec<String>,
    pub(super) col_names: Vec<String>,
    pub(super) sql: String,
}
impl CalcSqlItem {
    pub fn new(
        comment: String,
        divi_type: DiviType,
        ref_columns: Vec<String>,
        col_names: Vec<String>,
        sql: String,
    ) -> Self {
        Self {
            comment,
            divi_type,
            ref_columns,
            col_names,
            sql,
        }
    }
    pub(super) fn parse(&self) -> anyhow::Result<()> {
        let has_col = self.col_names.iter().any(|c| c == "DTID");
        if !has_col {
            anyhow::bail!("big_table_calc:insert中必须含有DTID字段");
        }
        let has_col = self.col_names.iter().any(|c| c == "EMID");
        if !has_col {
            anyhow::bail!("big_table_calc:insert中必须含有EMID字段");
        }
        let has_col = self.col_names.iter().any(|c| c == "CUID");
        if !has_col {
            anyhow::bail!("big_table_calc:insert中必须含有CUID字段");
        }
        let has_col = self.col_names.iter().any(|c| c == "CYNO");
        if !has_col {
            anyhow::bail!("big_table_calc:insert中必须含有CYNO字段");
        }
        Ok(())
    }
    pub(super) async fn run(&self,
        clinet: &mut clickhouse_rs::ClientHandle,
        date: &NaiveDate,
        back_where: &str,
        ac: &AhoCorasick,) -> anyhow::Result<()> {
        self.delete(clinet, date, back_where).await?;
        self.insert(clinet, date, back_where, ac).await?;
        sum_to_empl(clinet, &self.divi_type, &self.col_names, date, back_where).await?;
        Ok(())
    }
    async fn delete(
        &self,
        clinet: &mut clickhouse_rs::ClientHandle,
        date: &NaiveDate,
        back_where: &str,
    ) -> anyhow::Result<()> {
        let table_name = match self.divi_type {
            DiviType::CompBizFee | DiviType::CompAcco => "DFKB_COMP_CLIE_INDEX",
            _ => "DFKB_INDI_CLIE_INDEX",
        };
        let mut ins_cols_str = String::new();
        let mut sel_cols = String::new();
        let mut having_case = String::new();
        for col_name in self.col_names.iter() {
            ins_cols_str.push_str(col_name);
            if col_name == "DTID" || col_name == "EMID" || col_name == "CUID" || col_name == "CYNO"
            {
                sel_cols.push_str(col_name);
            } else {
                sel_cols.push_str("-sum(");
                sel_cols.push_str(col_name);
                sel_cols.push_str(") as ");
                sel_cols.push_str(col_name);
                having_case.push_str(col_name);
                having_case.push_str(">0 or ");
            }
            ins_cols_str.push_str(",");
            sel_cols.push_str(",");
        }
        sel_cols.pop();
        having_case.pop();
        having_case.pop();
        having_case.pop();
        having_case.pop();
        let date_str = date.format("%Y-%m-%d").to_string();
        //删除旧的数据
        let sql=format!("insert into {table_name}({ins_cols_str}) select {sel_cols} from {table_name} where DTID='{date_str}' {back_where} group by DTID,EMID,CUID,CYNO having {having_case}");
        clinet.execute(sql.as_str()).await?;
        Ok(())
    }
    async fn insert(
        &self,
        clinet: &mut clickhouse_rs::ClientHandle,
        date: &NaiveDate,
        back_where: &str,
        ac: &AhoCorasick,
    ) -> anyhow::Result<()> {
        let date_str = date.format("%Y-%m-%d").to_string();
        let replace_vals = &[&date_str, back_where];
        let mut wtr = vec![];
        ac.try_stream_replace_all(self.sql.as_bytes(), &mut wtr, replace_vals)
            .expect(format!("语句:{}字符串替换失败",self.comment).as_str());
        let sql = String::from_utf8(wtr)?;
        clinet.execute(sql.as_str()).await?;
        Ok(())
    }
}
static BIG_TABLE_CALC_CONF: Lazy<ArcSwapOption<&'static Vec<CalcSqlItem>>> =
    Lazy::new(|| ArcSwapOption::const_empty());
pub(super) fn init_big_table_calc() -> anyhow::Result<()> {
    let file_path = format!("{}/sqls/big_table_calc.sql", get_work_path());
    let sqls_str = std::fs::read_to_string(file_path)?;
    let sqls: Vec<&str> = sqls_str.split(';').collect();
    let mut sis: Vec<CalcSqlItem> = vec![];
    for sql in sqls {
        if sql.trim().is_empty() {
            continue;
        }
        let input: Vec<char> = sql.chars().collect();
        let (comment, divi_type_str, ref_columns, col_names) = sql_calc_item_parse()
            .parse(&input)
            .or_else(|err| anyhow::bail!("sql:{sql} 语句无效,{}", err))?;
        let dt = DiviType::from_str(&divi_type_str).or_else(|_err| {
            Err(anyhow::anyhow!(
                "语句{comment}:divi_type:{divi_type_str}无效",
            ))
        })?;
        let csi = CalcSqlItem::new(comment, dt, ref_columns, col_names, sql.to_string());
        csi.parse()?;
        sis.push(csi);
    }
    BIG_TABLE_CALC_CONF.store(Some(Arc::new(Box::leak(Box::new(sis)))));
    Ok(())
}

pub(super) fn get_ref_calc_sqls(col_names: &Vec<String>) -> Vec<&CalcSqlItem> {
    let csis=BIG_TABLE_CALC_CONF.load();
    let csis=csis.as_ref().unwrap();
    let mut res: Vec<&CalcSqlItem> = Vec::new();
    for csi in csis.iter() {
        let is_ref=col_names.iter().any(|col_name| csi.ref_columns.contains(col_name));
        if is_ref {
            res.push(csi);
        }
    }
    res
}
pub(super) fn get_calc_sqls_by_divi_type(divi_type: &DiviType) -> Vec<&CalcSqlItem>  {
    let csis=BIG_TABLE_CALC_CONF.load();
    let csis=csis.as_ref().unwrap();
    let mut res: Vec<&CalcSqlItem> = Vec::new();
    for csi in csis.iter() {
        if divi_type==&csi.divi_type {
            res.push(csi);
        }
    }
    res
}
pub(super) fn get_calc_sqls() -> &'static Vec<CalcSqlItem> {
    let csis=BIG_TABLE_CALC_CONF.load();
    let csis=csis.as_ref().unwrap();
    csis
}
